/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import units.CellType;
import units.MonkeyType;
import utils.FileUtils;
import utils.Handle;

/**
 *
 * @author jamie
 */
public class MakeDB {

    private Handle handle = Handle.getConnectedHandle("monkeys");
    private String sql;
    private File input = new File("/Users/jamie/monkeys/microarrays/perAnimal/cutDown/");
    private File input_DE_AP = new File("/Users/jamie/monkeys/microarrays/combined/cutDown/AP.txt");
    private File input_DE_MP = new File("/Users/jamie/monkeys/microarrays/combined/cutDown/MP.txt");
    private File input_DE_AL = new File("/Users/jamie/monkeys/microarrays/combined/cutDown/AL.txt");
    private File input_DE_ML = new File("/Users/jamie/monkeys/microarrays/combined/cutDown/ML.txt");
//    private double pValueCutoff = 0.05;

    public MakeDB() {
        System.out.println("making database");
        createSampleTable();
//        createTimePointsTable();
        createExpressionTable();
//        createDifferentialExpressionTable();
        addIndividualData();
//        addDifferentialExpressionData();

    }

    private void addIndividualData() {
        for (File f : input.listFiles()) {
            if (f.getName().startsWith("A") || f.getName().startsWith("M")) {
                System.out.println("adding data for file: " + f.getName());
                addData(f);
            }
        }
    }

    private void addDifferentialExpressionData() {
        addDifferentialExpressionData(input_DE_AL);
        addDifferentialExpressionData(input_DE_AP);
        addDifferentialExpressionData(input_DE_MP);
        addDifferentialExpressionData(input_DE_ML);
    }

    private void addDifferentialExpressionData(File deFile) {
        BufferedReader reader = FileUtils.getBufferedReader(deFile);
        String line;

        MonkeyType mt = MonkeyType.valueOf(deFile.getName().substring(0, 1));
        CellType ct = CellType.valueOf(deFile.getName().substring(1, 2));

        try {
            line = reader.readLine();
            String splitHeader[] = line.split("\t");
            int groupColIndex = -1;
            int entrezGeneColIndex = -1;


            for (int i = 0; i < splitHeader.length; i++) {
//                System.out.println(splitHeader[i]);
                if (splitHeader[i].equals("GROUP")) {
                    groupColIndex = i;
                } else if (splitHeader[i].contains("Entrez")) {
                    entrezGeneColIndex = i;
                }
            }
            ArrayList<Integer> timePoints = getTimePoints(splitHeader, groupColIndex);
//            System.out.println("groupColIndex " + groupColIndex);
//            System.out.println("entrezCol " + entrezGeneColIndex);
//            System.exit(0);
            int noOfTimepoints = groupColIndex / 5;

            while ((line = reader.readLine()) != null) {
                int timePointIndex = 0;
                String[] splitLine = line.split("\t");
                int probeId = Integer.parseInt(splitLine[0]);
                if (!splitLine[entrezGeneColIndex].equals("null")) {

                    int geneIds[] = getGeneIds(splitLine[entrezGeneColIndex]);
                    for (int gId : geneIds) {
                        for (int i = 1; i < groupColIndex; i += 5) {
                            double logExp = Double.parseDouble(splitLine[i]);
                            double var = Double.parseDouble(splitLine[i + 1]);
                            double pValue = Double.parseDouble(splitLine[i + 2]);
                            double pPrime = Double.parseDouble(splitLine[i + 3]);
                            double e = Double.parseDouble(splitLine[i + 4]);

                            int timePoint = timePoints.get(timePointIndex);
                            timePointIndex++;
                            if (timePointIndex == noOfTimepoints) {
                                timePointIndex = 0;
                            }
                            addDifferentialExpressionData(ct, mt, probeId, timePoint, gId, logExp, var, pValue, pPrime, e);


                        }
                    }
                }


            }
        } catch (IOException ex) {
            Logger.getLogger(MakeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void addData(File dataFile) {
        BufferedReader reader = FileUtils.getBufferedReader(dataFile);
        String line;

        MonkeyType mt = MonkeyType.valueOf(dataFile.getName().substring(0, 1));
        CellType ct = CellType.valueOf(dataFile.getName().substring(1, 2));
        String sampleName = dataFile.getName().split("[.]")[0];
        int sampleId = addSampleData(sampleName, mt, ct);

        try {
            line = reader.readLine();
            String splitHeader[] = line.split("\t");
            int groupColIndex = -1;
            int entrezGeneColIndex = -1;


            for (int i = 0; i < splitHeader.length; i++) {
//                System.out.println(splitHeader[i]);
                if (splitHeader[i].equals("GROUP")) {
                    groupColIndex = i;
                } else if (splitHeader[i].contains("Entrez")) {
                    entrezGeneColIndex = i;
                }
            }
            ArrayList<Integer> timePoints = getTimePoints(splitHeader, groupColIndex);
//            System.out.println("groupColIndex " + groupColIndex);
//            System.out.println("entrezCol " + entrezGeneColIndex);
//            System.exit(0);
            int noOfTimepoints = groupColIndex / 5;

            while ((line = reader.readLine()) != null) {
                int timePointIndex = 0;
                String[] splitLine = line.split("\t");
                int probeId = Integer.parseInt(splitLine[0]);
                if (!splitLine[entrezGeneColIndex].equals("null")) {

                    int geneIds[] = getGeneIds(splitLine[entrezGeneColIndex]);
                    for (int gId : geneIds) {
                        for (int i = 1; i < groupColIndex; i += 5) {
                            double logExp = Double.parseDouble(splitLine[i]);
                            double var = Double.parseDouble(splitLine[i + 1]);
                            double pValue = Double.parseDouble(splitLine[i + 2]);
                            double pPrime = Double.parseDouble(splitLine[i + 3]);
                            double e = Double.parseDouble(splitLine[i + 4]);
                            int timePoint = timePoints.get(timePointIndex);
                            timePointIndex++;
                            if (timePointIndex == noOfTimepoints) {
                                timePointIndex = 0;
                            }
                            addExpressionData(sampleId, timePoint, probeId, gId, logExp, var, pValue, pPrime, e);

                        }
                    }
                }


            }
        } catch (IOException ex) {
            Logger.getLogger(MakeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private int addSampleData(String sampleName, MonkeyType mt, CellType ct) {
        sql = "insert into sample values(0,'" + sampleName + "', '" + mt.name() + "', '" + ct.name() + "')";
        handle.runSQLupdate(sql);
        sql = "select id from sample where sampleName='" + sampleName + "'";
        ResultSet rs = handle.runSQLquery(sql);
        try {
            if (rs.next()) {
                return rs.getInt("id");
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(MakeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;

    }

    private void addExpressionData(int sampleId, int timePoint, int probeId, int gId, double logExp, double var, double pValue, double pPrime, double e) {
        sql = "insert into expression values(0," + sampleId + ", " + timePoint + ", " +probeId+", "+ gId + ", " + logExp + ", " + var + "," + pValue + "," + pPrime + ", " + e + ")";
        System.out.println(sql);
        handle.runSQLupdate(sql);
    }

    private void addDifferentialExpressionData(CellType cellType, MonkeyType monkeyType, int probeId, double timePoint, double gId, double logExp, double var, double pValue, double pPrime, double e) {
        sql = "insert into differentialExpression values(0,"+probeId+", '" + monkeyType.name() + "', '" + cellType.name() + "', "+ timePoint + ", " + gId + ", " + logExp + ", " + var + "," + pValue + "," + pPrime + ", " + e + ")";
        System.out.println(sql);
        handle.runSQLupdate(sql);
    }

    private void createExpressionTable() {
        sql = "drop table if exists expression";
        handle.runSQLupdate(sql);
        sql = "create table expression (id int(10) not null primary key auto_increment, sampleId int(4) not null, timePoint int(4) not null,  probeId int(10) not null, gId int(12) not null, logExp double not null, var double not null, pValue double not null, pPrime double not null, eValue double not null)";
        handle.runSQLupdate(sql);
        sql = "create index sampleId_index on expression(sampleId)";
        handle.runSQLupdate(sql);
        sql = "create index timePoint_index on expression(timePoint)";
        handle.runSQLupdate(sql);
    }

    private void createDifferentialExpressionTable() {
        sql = "drop table if exists differentialExpression";
        handle.runSQLupdate(sql);
        sql = "create table differentialExpression (id int(10) not null primary key auto_increment, probeId int(10) not null, monkeyType char(1) not null, cellType char(1) not null, timePoint int(4) not null, gId int(12) not null, logExp double not null, var double not null, pValue double not null, pPrime double not null, eValue double not null)";
        handle.runSQLupdate(sql);
        sql = "create index timePoint_index on differentialExpression(timePoint)";
        handle.runSQLupdate(sql);
        sql = "create index timePoint_gId on differentialExpression(gId)";
        handle.runSQLupdate(sql);
        sql = "create index timePoint_probe on differentialExpression(probeId)";
        handle.runSQLupdate(sql);
    }

    private void createSampleTable() {
        sql = "drop table if exists sample";
        handle.runSQLupdate(sql);
        sql = "create table sample (id int(10) not null primary key auto_increment, sampleName varchar(30) not null, monkeyType char(3) not null, cellType char(3) not null)";
        handle.runSQLupdate(sql);
        sql = "create index sampleName_index on sample(sampleName)";
        handle.runSQLupdate(sql);
        sql = "create index cellType_index on sample(cellType)";
        handle.runSQLupdate(sql);
        sql = "create index monkeyType_index on sample(monkeyType)";
        handle.runSQLupdate(sql);
    }

    private ArrayList<Integer> getTimePoints(String[] splitHeader, int groupColIndex) {
        ArrayList<Integer> times = new ArrayList<Integer>();
        for (int i = 1; i < groupColIndex; i += 5) {
//            System.out.println(splitHeader[i]);
            String substring = splitHeader[i].substring(6, splitHeader[i].length() - 3);

            int time = Integer.parseInt(substring);
            times.add(time);
        }
        return times;
    }

    private int[] getGeneIds(String string) {
        int[] arr;
        if (string.contains("|")) {
            String split[] = string.split("\\|");
//            System.out.println(string+" "+split.length);
            arr = new int[split.length];
            int i = 0;
            for (String s : split) {

                arr[i] = Integer.parseInt(s);
                i++;
            }
        } else {
            arr = new int[1];
            arr[0] = Integer.parseInt(string);
        }
        return arr;
    }
//    private void createTimePointsTable() {
//        sql = "drop table if exists timePoints";
//        handle.runSQLupdate(sql);
//        sql = "create table arrays (int id not null primary key auto_increment, sampleId int not null, timePoint int(4) not null)";
//        handle.runSQLupdate(sql);
//        sql = "create index sampleId_index on arrays(sampleId)";
//        handle.runSQLupdate(sql);
//        sql = "create index timePoint_index on arrays(timePoint)";
//        handle.runSQLupdate(sql);
//    }
}
